﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel;

namespace qna.Business
{
    [DataObject]
    public class Answer: BusinessObject
    {
        //Varibales
        [DataObjectField(true, true)]
        public int Id { get; set; }

        [DataObjectField(false, false)]
        public string Content { get; set; }

        [DataObjectField(false, false)]
        public int UserId { get; set; }

        [DataObjectField(false, false)]
        public int QuestionID { get; set; }

        [DataObjectField(false, false)]
        public int Rating { get; set; }

        private DateTime dt;
        [DataObjectField(false, false)]
        public DateTime DateTime
        {
            get { return dt == DateTime.MinValue ? DateTime.Now : dt;}
            set { dt = value; }
        }

        //Overrride methods from BusinessObject
        protected override BusinessObject New()
        {
            return new Answer();
        }

        protected override string TableName
        {
            get { return "answer"; }
        }

        public override IDictionary<string, object> Attributes
        {
            get
            {
                IDictionary<string, object> attrs = new Dictionary<string, object>();
                attrs["Id"] = Id;
                attrs["Content"] = Content;
                attrs["Datetime_Updated"] = DateTime;
                attrs["User_Id"] = UserId;
                attrs["question_id"] = QuestionID;
                if (Rating != 0)
                {
                    attrs["rating"] = Rating;
                }
                return attrs;
            }
        }

        public override void ReadAttributes(System.Data.SqlClient.SqlDataReader dr)
        {
            Id = (int)dr["Id"];
            Content = dr["Content"].ToString();
            DateTime = (DateTime)dr["Datetime_Updated"];
            UserId = (int)dr["User_Id"];
            QuestionID = (int)dr["question_id"];
            if (dr["rating"] != DBNull.Value)
            {
                Rating = (int)dr["rating"];
            }
        }

        [DataObjectMethod(DataObjectMethodType.Select, false)]
        public IList<Answer> GetByQuestionID(int qID)
        {
            IDictionary<string, object> answerPar = new Dictionary<string, object>();

            //string sql = "SELECT * FROM " + TableName + " WHERE question_id=@question_id";
            string sql = "select A.id, A.content, A.datetime_updated, A.user_id, A.question_id, A.rating, COUNT(answer_id) as r from " + TableName + " A left join answer_rating on A.id = answer_id where question_id = @question_id group by A.id, A.content, A.datetime_updated, A.user_id, A.question_id, A.rating order by r desc";
            answerPar.Add("@question_id", qID);
            
            List<BusinessObject> list = (List<BusinessObject>)QueryAll(sql, answerPar);
            return list.ConvertAll<Answer>(delegate(BusinessObject bo)
            {
                return (Answer)bo;
            });
        }
        [DataObjectMethod(DataObjectMethodType.Delete, true)]
        public bool DeleteAnswer(int id)
        {
            string sql = "DELETE FROM Answer WHERE id = @aID";
            IDictionary<string, object> param = new Dictionary<string, object>();
            param["@" + PrimaryKey] = Attributes[PrimaryKey];
            return ExecuteNonQuery(sql, param) > 0;
        }

        /// <summary>
        /// Method to set the rating for Answers
        /// </summary>
        /// <param name="aID">ID of answer to rate</param>
        /// <param name="uID">ID of user who is rating</param>
        public bool SetAnswerRating(int aID, int uID)
        {
            bool returnVal = false;
            IDictionary<string, object> answerPar = new Dictionary<string, object>();

            string sqlInsert = "INSERT INTO answer_rating VALUES(@answer_id, @user_id)";
            string sqlDelete = "DELETE FROM answer_rating WHERE answer_id = @answer_id AND user_id = @user_id";
            answerPar.Add("@answer_id", aID);
            answerPar.Add("@user_id", uID);
            bool userRated = CheckIfUserRated(aID, uID);
            if (userRated == true)
            {
                int deleteRowsAffected = ExecuteNonQuery(sqlDelete, answerPar);
                if (deleteRowsAffected > 0)
                    returnVal = true;
                else
                    returnVal = false;
            }
            else
            {
                int insertRowsAffected = ExecuteNonQuery(sqlInsert, answerPar);

                if (insertRowsAffected > 0)
                    returnVal = true;
                else
                    returnVal = false;
            }

            return returnVal;
        }

        /// <summary>
        /// Method to get the total rating for an Answer
        /// </summary>
        /// <param name="aID">ID of answer to get rating of</param>
        public int GetAnswerRating(int aID)
        {
            IDictionary<string, object> answerPar = new Dictionary<string, object>();
            string sql = "SELECT COUNT(answer_id) FROM answer_rating WHERE answer_id = @answer_id";
            answerPar.Add("@answer_id", aID);

            int totalRating = ExecuteScalar(sql, answerPar);
            return totalRating;
        }

        /// <summary>
        /// Method to check if the user has rated
        /// </summary>
        /// <param name="aID">ID of answer to rate</param>
        /// <param name="uID">ID of user who is rating</param>
        public bool CheckIfUserRated(int aID, int uID)
        {
            bool returnVal = false;

            IDictionary<string, object> answerPar = new Dictionary<string, object>();
            string sqlSelect = "SELECT COUNT(user_id) FROM answer_rating WHERE answer_id = @answer_id AND user_id = @user_id";
            answerPar.Add("@answer_id", aID);
            answerPar.Add("@user_id", uID);
            int selectRowsAffected = ExecuteScalar(sqlSelect, answerPar);
            if (selectRowsAffected > 0)
                returnVal = true;
            else
                returnVal = false;

            return returnVal;
        }

    }
}